package com.carlinfo.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 * 	数据库的工具类
 * 
 * 	~单例模式
 * 		new的时候调构造方法
 * */
public class DbUtil
{
	
	/*	私有的构造方法只能内部 使用	
	 * 	静态的方法中只能使用 静态的对象和方法
	 * */
	private static DbUtil INSTANCE = new DbUtil();
	
	private String driver =ConstantFinalUtil.resourveBundle.getString("jdbc.driver");
	private String url = ConstantFinalUtil.resourveBundle.getString("jdbc.url");
	private String user =ConstantFinalUtil.resourveBundle.getString("jdbc.user");
	private String password =ConstantFinalUtil.resourveBundle.getString("jdbc.password");

	/*	私有的 构造方法  不外new	*/
	private DbUtil()
	{
		try
		{
			/*	单例模式 构造方法只new一次 	*/
			Class.forName(driver);
		} catch (ClassNotFoundException e)
		{
			System.out.println("=驱动加载失败了=");
			e.printStackTrace();
		}
	}
	/*	获取连接  	*/
	public Connection getConnection()
	{
		Connection connection = null;
		try
		{
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return connection;
	}
	/*	封装 关闭水管	*/
	public void closeAll(Connection connection, PreparedStatement preparedStatemet,
			ResultSet resultSet)
	{
		try
		{
			if(resultSet != null)
			{
				resultSet.close();
				resultSet = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		try
		{
			if(preparedStatemet != null)
			{
				preparedStatemet.close();
				preparedStatemet = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		try
		{
			if(connection != null)
			{
				connection.close();
				connection = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * 	单例模式  设置一个静态方法  
	 * 	返回值是自己本身 	
	 * 	*/
	public static DbUtil getInstance()
	{
		return INSTANCE;
	}
	
	/*	封装jdbc	
	 * 	四步走
	 * */
	private void JDBCtest()
	{
		String sql = "select*from a_dynasty;";
		
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		/*	加载驱动	*/
		try
		{
			Class.forName(driver);
			/*	获取连接	*/
			conn = DriverManager.getConnection(url, user, password);
			/*	传入sql语句	*/
			pst = conn.prepareStatement(sql);
			/*	结果集	executeQuery查询的方法  用if 或者 循环*/
			rs = pst.executeQuery();
			/* 打印标题 */
			System.out.println("id\t名字\t首都\t国祚\t状态\t开国时间\t\t\t创建时间\t\t\t更新时间\t\t\t发布时间");
			while(rs.next())
			{
				/*	赋值	*/
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String capital = rs.getString("capital");
				short age = rs.getShort("age");
				/* 时间戳 */
				Date st = rs.getTimestamp("st");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");
				Date pubTime = rs.getTimestamp("pubTime");
				byte status = rs.getByte("status");
				/* 打印 */
				System.out.println(id + "\t" + name + "\t" + capital + "\t"
						+ age + "\t" + status + "\t" + st.toLocaleString() + "\t"
						+ createTime.toLocaleString() + "\t" + updateTime.toLocaleString() 
						+ "\t" + pubTime.toLocaleString());
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(pst != null)
				{
					pst.close();
					pst = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(conn != null)
				{
					conn.close();
					conn = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
	/*	使用的时候直接调用	*/
	public void newJDBC()
	{
		String sql = "";
		DbUtil dbUtil = DbUtil.getInstance();
		/*	获取连接	*/
		Connection con = dbUtil.getConnection();
		PreparedStatement pst = null ;
		ResultSet rs = null;
		try
		{
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			dbUtil.closeAll(con, pst, rs);
		}
	}
	public static void main(String[] args)
	{
		DbUtil dbUtil = DbUtil.getInstance();
		dbUtil.JDBCtest();
	}
}
